#!/usr/bin/env bash
source /etc/profile

if [ -n "$1" ]
then
  do_date=$1
else
  do_date=$(date -d "-1 day" +%F)
fi

sql="
insert overwrite table dws.dws_member_retention_day
partition(dt='$do_date')
(
  select t2.device_id,
    t2.uid,
    t2.app_v,
    t2.os_type,
    t2.language,
    t2.channel,
    t2.area,
    t2.brand,
    t2.dt add_date,
    1
  from dws.dws_member_start_day t1
    join dws.dws_member_add_day t2
      on t1.device_id = t2.device_id
  where t2.dt=date_add('$do_date', -1)
    and t1.dt='$do_date'
  union all
  select t2.device_id,
    t2.uid,
    t2.app_v,
    t2.os_type,
    t2.language,
    t2.channel,
    t2.area,
    t2.brand,
    t2.dt add_date,
    2
  from dws.dws_member_start_day t1
    join dws.dws_member_add_day t2
      on t1.device_id = t2.device_id
  where t2.dt=date_add('$do_date', -2)
    and t1.dt='$do_date'
  union all
  select t2.device_id,
    t2.uid,
    t2.app_v,
    t2.os_type,
    t2.language,
    t2.channel,
    t2.area,
    t2.brand,
    t2.dt add_date,
    3
  from dws.dws_member_start_day t1
    join dws.dws_member_add_day t2
      on t1.device_id = t2.device_id
  where t2.dt=date_add('$do_date', -3)
    and t1.dt='$do_date'
);
"

hive -e "$sql"